package cz.najman.emdroid.provider;

import cz.najman.emdroid.mappings.Event;
import cz.najman.emdroid.mappings.EventInstitutionManager;
import cz.najman.emdroid.mappings.PaymentInformation;
import cz.najman.emdroid.mappings.RegistrationState;
import cz.najman.emdroid.mappings.User;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper{

	/**
	 * Name of the database
	 */
	private static String DATABASE_NAME = "emdroid.db";
	
	/**
	 * Version of the database
	 */
	private static int DATABASE_VERSION = 34;
	
	/**
	 * String used for creating database table of events
	 */
	private static final String CREATE_EVENT_TABLE = "CREATE TABLE IF NOT EXISTS " + Event.TABLE_NAME + " (" +
			Event._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
			Event.REMOTE_ID + " INTEGER UNIQUE," +
			Event.EVENT_NAME + " TEXT NOT NULL," +
			Event.REGISTRATION_START + " INTEGER NOT NULL," + 
			Event.REGISTRATION_END + " INTEGER NOT NULL," + 
			Event.START_DATE + " INETEGER NOT NULL," +
			Event.END_DATE + " INTEGER NOT NULL," + 
			Event.VISIBLE + " INTEGER NOT NULL," +
			Event.STATE_FLAG + " INTEGER" + 
			");";
	
	
	private static final String CREATE_USER_TABLE = "CREATE TABLE IF NOT EXISTS " + User.TABLE_NAME + " (" +
			User._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
			User.REMOTE_ID + " INTEGER UNIQUE," +
			User.EMAIL + " TEXT UNIQUE," + 
			User.PASSWORD + " TEXT NOT NULL," + 
			User.INSTITUTION_ID + " INTEGER NOT NULL," +
			User.COUNTRY_ID + " INTEGER NOT NULL," +
			User.NAME + " TEXT NOT NULL," + 
			User.SURNAME + " TEXT NOT NULL," +
			User.NICKNAME + " TEXT," + 
			User.GENDER + " INTEGER NOT NULL," + 
			User.ENABLED + " INTEGER NOT NULL," + 
			User.PHONE_NUMBER + " TEXT NOT NULL," +
			User.STATE_FLAG + " INTEGER" + 
			");";
	
	private static final String CREATE_PAYMENT_INFORMATION_TABLE  = "CREATE TABLE IF NOT EXISTS " + PaymentInformation.TABLE_NAME + " (" +
			PaymentInformation._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
			PaymentInformation.VALUE_TYPE + " TEXT NOT NULL," +
			PaymentInformation.ACTIVE_FROM + " INTEGER NOT NULL," + 
			PaymentInformation.ACTIVE_TO + " INTEGER NOT NULL," + 
			PaymentInformation.VALUE + " INTEGER NOT NULL," + 
			PaymentInformation.EVENT_ID + " INTEGER NOT NULL," +
			PaymentInformation.STATE_FLAG + " INTEGER" + 
			");";
	
	private static final String CREATE_REGISTRATION_STATES_TABLE = "CREATE TABLE IF NOT EXISTS " + RegistrationState.TABLE_NAME + " (" +
			RegistrationState._ID + " INTEGER PRIMARY KEY AUTOINCREMENT ," +
			RegistrationState.REGISTRATION_DATE + " INTEGER NOT NULL," +
			RegistrationState.STATE + " INTEGER NOT NULL," +
			RegistrationState.PAYMENT_DATE + " INTEGER," +
			RegistrationState.PAYMENT_VALUE + " INTEGER," +
			RegistrationState.STORNO_VALUE + " INTEGER," +
			RegistrationState.USER_ID + " INTEGER NOT NULL," +
			RegistrationState.EVENT_ID + " INTEGER NOT NULL," +
			RegistrationState.COMMENT + " TEXT," +
			RegistrationState.STORNO_REASON + " TEXT," +
			RegistrationState.STATE_FLAG + " INTEGER" + 
			");";
	
	private static final String CREATE_EVENT_MANAGERS_TABLE = "CREATE TABLE IF NOT EXISTS " + EventInstitutionManager.TABLE_NAME + " (" +
			EventInstitutionManager._ID + " INTEGER PRIMARY KEY AUTOINCREMENT ," +
			EventInstitutionManager.MANAGER_ID + " INTEGER NOT NULL," +
			EventInstitutionManager.INSTITUTION_ID + " INTEGER NOT NULL," +
			EventInstitutionManager.EVENT_ID + " INTEGER NOT NULL," +
			EventInstitutionManager.STATE_FLAG + " INTEGER" + 
			");";
	
	private static final String CREATE_VIEW_REG_STATE_EVENTS = "CREATE VIEW IF NOT EXISTS " + DatabaseView.VIEW_REG_STATES_EVENTS + 
			" AS SELECT " + 
			RegistrationState.TABLE_NAME + "." + RegistrationState._ID + " AS " + RegistrationState._ID + "," +
			RegistrationState.TABLE_NAME + "." + RegistrationState.REGISTRATION_DATE + " AS " + RegistrationState.REGISTRATION_DATE + "," +
			RegistrationState.TABLE_NAME + "." + RegistrationState.STATE + " AS " + RegistrationState.STATE + "," +
			RegistrationState.TABLE_NAME + "." + RegistrationState.PAYMENT_DATE + " AS " + RegistrationState.PAYMENT_DATE + "," +
			RegistrationState.TABLE_NAME + "." + RegistrationState.PAYMENT_VALUE + " AS " + RegistrationState.PAYMENT_VALUE + "," +
			RegistrationState.TABLE_NAME + "." + RegistrationState.STORNO_VALUE + " AS " + RegistrationState.STORNO_VALUE + "," +
			RegistrationState.TABLE_NAME + "." + RegistrationState.USER_ID + " AS " + RegistrationState.USER_ID + "," +
			RegistrationState.TABLE_NAME + "." + RegistrationState.EVENT_ID + " AS " + RegistrationState.EVENT_ID + "," +
			RegistrationState.TABLE_NAME + "." + RegistrationState.COMMENT + " AS " + RegistrationState.COMMENT + "," +
			RegistrationState.TABLE_NAME + "." + RegistrationState.STORNO_REASON + " AS " + RegistrationState.STORNO_REASON + "," +
			RegistrationState.TABLE_NAME + "." + RegistrationState.STATE_FLAG + " AS " + RegistrationState.STATE_FLAG + "," +
			Event.TABLE_NAME + "." + Event.EVENT_NAME + " AS " + Event.EVENT_NAME  + "," +
			Event.TABLE_NAME + "." + Event.START_DATE + " AS " + Event.START_DATE  + "," +
			Event.TABLE_NAME + "." + Event.END_DATE + " AS " + Event.END_DATE + "," +
			Event.TABLE_NAME + "." + Event.REGISTRATION_START + " AS " + Event.REGISTRATION_START + "," +
			Event.TABLE_NAME + "." + Event.REGISTRATION_END + " AS " + Event.REGISTRATION_END  + "," +
			Event.TABLE_NAME + "." + Event.VISIBLE + " AS " + Event.VISIBLE +			
			" FROM " + Event.TABLE_NAME +
			" JOIN " + RegistrationState.TABLE_NAME + " ON " + 
			RegistrationState.TABLE_NAME + "." + RegistrationState.EVENT_ID  + 
			" = " + Event.TABLE_NAME + "." + Event._ID  + ";";
	
	private static final String CREATE_VIEW_EVENTS_AVAILABLE = "CREATE VIEW IF NOT EXISTS " + DatabaseView.VIEW_EVENTS_AVAILABLE + 
			" AS SELECT events._id, event_name, registration_start, registration_end, start_date, end_date FROM events join event_managers on events._id = event_managers.event_id WHERE visible like 'true' EXCEPT SELECT events._id, event_name, registration_start, registration_end, start_date, end_date FROM events JOIN registration_states on events._id = registration_states.event_id WHERE visible like 'true';";
			
	
	public DatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}
	
	public DatabaseHelper(Context context, String name, CursorFactory factory, int version) {
		super(context, name, factory, version);		
	}	

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_EVENT_TABLE);
		db.execSQL(CREATE_USER_TABLE);
		db.execSQL(CREATE_PAYMENT_INFORMATION_TABLE);
		db.execSQL(CREATE_REGISTRATION_STATES_TABLE);
		db.execSQL(CREATE_EVENT_MANAGERS_TABLE);
		db.execSQL(CREATE_VIEW_REG_STATE_EVENTS);
		db.execSQL(CREATE_VIEW_EVENTS_AVAILABLE);	
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + Event.TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + User.TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + PaymentInformation.TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + RegistrationState.TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + EventInstitutionManager.TABLE_NAME);
		db.execSQL("DROP VIEW IF EXISTS " + DatabaseView.VIEW_REG_STATES_EVENTS);
		db.execSQL("DROP VIEW IF EXISTS " + DatabaseView.VIEW_EVENTS_AVAILABLE);
		db.execSQL(CREATE_EVENT_TABLE);		
		db.execSQL(CREATE_USER_TABLE);
		db.execSQL(CREATE_PAYMENT_INFORMATION_TABLE);
		db.execSQL(CREATE_REGISTRATION_STATES_TABLE);
		db.execSQL(CREATE_EVENT_MANAGERS_TABLE);
		db.execSQL(CREATE_VIEW_REG_STATE_EVENTS);
		db.execSQL(CREATE_VIEW_EVENTS_AVAILABLE);
		
	}

}
